package dbscripts.insertregionadmin


import java.sql.Connection
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.util.concurrent.atomic.AtomicInteger

/***
 * 在每个区划下增加sysadmin角色对应的区划管理员，可以重复执行
 */


//数据库地址
def url = "jdbc:oracle:thin:@10.108.6.15:1521:wfw"
//用户名
def username = "JXPRECRUX1221"
//密码
def password = "yygov1221"
Class.forName("oracle.jdbc.driver.OracleDriver")
def conn = DriverManager.getConnection(url, username, password)
conn.setAutoCommit(false)
//sysadmin
def sysAdminRoleId = getSysAdminRoleId(conn)
if (sysAdminRoleId == -1) {
    println("sysadmin角色不存在")
    return -1;
}

//组织权限类型ID
def orgTypeId = getOrgTypeId(conn)

List<Region> regions = getAllRegion(conn)

//批量插入用户sql
def batchInsertUserSql = "INSERT INTO USERCENTER_USER (ID,LAST_MODIFIED_VERSION, AUDITED,CODE, ENABLED, LOCKED,  NAME, NICKNAME, ORGANIZATION, PASSWORD, PASSWORD_CHANGED, REGION, BELONG_TYPE, GENDER, USER_TYPE) " +
        "VALUES (:userId, 0, 1,:code, 1, 0, :username,:nickname, :orgId, :password, 1, :regionId, 'REGION', 1, 'OTHER'"
//批量插入用户insert
def batchInsertUserPs = conn.prepareStatement(batchInsertUserSql)

//批量插入用户任职sql
def batchInsertOfficeSql = "insert into usercenter_user_office(id,LAST_MODIFIED_VERSION,organization,region,user_id) values(:id,0,:orgId,:regionId,:userId)"
//批量插入用户任职ps
def batchInsertOfficePs = conn.prepareStatement(batchInsertOfficeSql)


//批量插入用户角色sql
def batchInsertRoleSql = "insert into permission_user_func_group(id,LAST_MODIFIED_VERSION,organization,function_group_id,user_id) values(:id,0,:orgId,:roleId,:userId)"
//批量插入用户角色ps
def batchInsertRolePs = conn.prepareStatement(batchInsertRoleSql)


//批量插入用户组织权限sql
def batchInsertPaSql = "insert into permission_per_assignment(id,last_modified_version,type_id,resource_id,resource_name,can_access,subject_id,subject_type,permission_range,organization,function_group_id) values(:id,0,:type_id,:resource_id,:resource_name,1,:subject_id,'USER','SAME_LOWER_LEVEL',:organization,:function_group_id)"
//批量插入用户组织权限ps
def batchInsertPaPs = conn.prepareStatement(batchInsertPaSql)

//主逻辑
regions.forEach({ r ->
    //插入用户
    long userId = batchAdminUser(conn, batchInsertUserPs, r)
    if (userId != -1) {
        //插入用户任职
        batchAdminOffice(conn, batchInsertOfficePs, userId, r)
        //插入用户角色
        batchInsertUserRole(conn, batchInsertRolePs, userId, sysAdminRoleId, r)
        //插入组织权限
        batchInsertPa(conn, batchInsertPaPs, userId, r, orgTypeId)
    }
})
println("开始提交事务")
batchInsertUserPs.executeBatch()
batchInsertOfficePs.executeBatch()
batchInsertRolePs.executeBatch()
batchInsertPaPs.executeBatch()
conn.commit()
println("事务提交完成")
println("开始释放资源")
batchInsertUserPs.close()
batchInsertOfficePs.close()
batchInsertRolePs.close()
batchInsertPaPs.close()
println("释放资源完成")


static void batchInsertPa(Connection connection, PreparedStatement batchInsertRolePs, long userId, long roleId, Region region, long orgTypeId) {
    //:id,0,:type_id,:resource_id,:resource_name,1,:subject_id,'USER','SAME_LOWER_LEVEL',:organization,:function_group_id
    def ps = connection.prepareStatement("select count(id) as cou from permission_per_assignment where subject_id=:userId and type_id=:orgTypeId and subject_type='USER' and resource_id=:resourceId")
    //subject_id=:userId and type_id=:orgTypeId and subject_type='USER' and resource_id=:resourceId
    ps.setLong(1, userId)
    ps.setLong(2, orgTypeId)
    ps.setString(3, "id in (${region.id})")
    def rs = ps.executeQuery()
    rs.next()
    def count = rs.getInt("cou")
    if (count > 0) {
        println("用户${userId}已经在区划${region.name}(${region.code})已经分配组织权限，不需要插入")
    } else {
        //:id,0,:type_id,:resource_id,:resource_name,1,:subject_id,'USER','SAME_LOWER_LEVEL',:organization,:function_group_id
        batchInsertRolePs.setLong(1, GenId.generate())
        batchInsertRolePs.setLong(2, orgTypeId)
        batchInsertRolePs.setString(3, "id in (${region.id})")
        batchInsertRolePs.setString(4, region.name)
        batchInsertRolePs.setLong(5, userId)
        batchInsertRolePs.setLong(6, region.id)
        batchInsertRolePs.setLong(7, roleId)
        batchInsertRolePs.addBatch()
    }
    rs.close()
    ps.close()
}

static void batchInsertUserRole(Connection connection, PreparedStatement batchInsertRolePs, long userId, long roleId, Region region) {
    //:id,0,:orgId,:roleId,:userId
    def ps = connection.prepareStatement("select count(id) as cou from permission_user_func_group where user_id=:userId and function_group_id=:roleId")
    def rs = ps.executeQuery()
    rs.next()
    def count = rs.getInt("cou")
    if (count > 0) {
        println("用户${userId}已经在区划${region.name}(${region.code})分配角色记录，不需要插入")
    } else {
        batchInsertRolePs.setLong(1, GenId.generate())
        batchInsertRolePs.setLong(2, region.id)
        batchInsertRolePs.setLong(3, roleId)
        batchInsertRolePs.setLong(4, userId)
        batchInsertRolePs.addBatch()
        println("用户${userId}在区划${region.name}(${region.code})分配角色${roleId}")
    }
    rs.close()
    ps.close()
}

static long batchAdminUser(Connection connection, PreparedStatement batchInsertUserPs, Region region) {
    def userId = GenId.generate()
    def regionAdminCode = region.code + "sys"
    def regionAdminName = region.name + "默认系统管理员"
    def ps2 = connection.prepareStatement("select id from usercenter_user where code=:userCode")
    def rs = ps2.executeQuery()
    if (rs.next()) {
        def pwd = "\$2a\$10\$9/4BGZsv6CbJ5DmzWwM5DeOyoJWbz.wJAcgquq9cUh8G3gUdTnDMC";
        batchInsertUserPs.setLong(1, userId)
        batchInsertUserPs.setString(2, regionAdminCode)
        batchInsertUserPs.setString(3, regionAdminName)
        batchInsertUserPs.setString(4, regionAdminName)
        batchInsertUserPs.setLong(5, region.id)
        batchInsertUserPs.setString(6, pwd)
        batchInsertUserPs.setLong(7, region.id)
        batchInsertUserPs.addBatch()
    } else {
        println("${regionAdminName}(${regionAdminCode})已存在不插入")
        userId = rs.getLong("id")
    }
    rs.close()
    ps2.close()
    return userId
}

static void batchAdminOffice(Connection connection, PreparedStatement batchInsertOfficePs, long userId, Region region) {
    def countQuery = connection.prepareStatement("select count(id) as cou from usercenter_user_office where user_id=:userId and organization=:orgId")
    def rs = countQuery.executeQuery()
    rs.next()
    def count = rs.getInt("cou")
    if (count > 0) {
        println("用户${userId}已经在区划${region.name}(${region.code})下任职不需要插入任职记录")
    } else {
        batchInsertOfficePs.setLong(1, GenId.generate())
        batchInsertOfficePs.setLong(2, region.id)
        batchInsertOfficePs.setLong(3, region.id)
        batchInsertOfficePs.setLong(4, userId)
        batchInsertOfficePs.addBatch()
        println("用户${userId}在区划${region.name}(${region.code})下插入任职记录")
    }
    rs.close()
    countQuery.close()
}


List<Region> getAllRegion(Connection connection) {
    def ps = connection.prepareStatement("select id,code,name from org_region")
    def rs = ps.executeQuery()
    def regions = []
    while (rs.next()) {
        def id = rs.getLong("id")
        def code = rs.getString("code")
        def name = rs.getString("name")
        regions.add(new Region(id: id, code: code, name: name))
    }
    rs.close()
    println("总共有${regions.size()}个区划")
    return regions
}


//查找sysadmin角色
long getSysAdminRoleId(Connection connection) {
    def ps = connection.prepareStatement("select id  from permission_func_group where code=:code")
    ps.setString(1, "sysadmin");
    def rs = ps.executeQuery()
    def roleId = -1
    if (rs.next()) {
        roleId = rs.getLong("id")
        println("sysadmin角色的ID是${roleId}")
    } else {
        println("未找到sysadmin角色")
    }
    rs.close()
    ps.close()
    return roleId;
}

//获取组织类型ID
static long getOrgTypeId(Connection connection) {
    def orgTypePs = connection.prepareStatement("select id from permission_per_type where code=:code")
    orgTypePs.setString(1, "Organization")
    def rs = orgTypePs.executeQuery()
    rs.next()
    def orgTypeId = rs.getLong("id")
    rs.close()
    orgTypePs.close()
    return orgTypeId;
}

class Region {
    long id
    String code
    String name
}

class GenId {
    static AtomicInteger seq = new AtomicInteger(0);
    static long workId = (long) (new Random(System.nanoTime())).nextInt(2048);

    static long generate() {
        return System.currentTimeMillis() << 21 | workId << 10 | (long) seq.getAndUpdate({ operand ->
            ++operand;
            return operand % 1024;
        });
    }
}
